package com.subway.babynote.db;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import com.subway.babynote.syncdata.tableName;

public class TableInfoList
{
	public static final TableInfoList instance = new TableInfoList();
	public static class TableInfo {
		public String name = null;
		public String createsql = null;
		public String selectsql = null;
		public String insertsql = null;
		public String updatesql = null;
		static final String delsql = "delete from ";
		public String get_delsql()
		{
			String sql = delsql + name;
			return sql;
		}
		public String get_delsql_1row()
		{
			String sql =  "UPDATE " + name +
			" SET mark = 0 , ts = ?" +
			" WHERE _id=?";
			return sql;
		}
		public String get_tssql()
		{
			String sql = "SELECT MAX(ts) as ts FROM " + this.name;
			return sql;
		}
		public String get_tssql2()
		{
			String sql = "SELECT MAX(ts) as ts, client_id FROM " + this.name +
			"GROUP BY client_id";
			return sql;
		}
	}
	
	
	
	public Map<String,TableInfo> m = new TreeMap<String,TableInfo>();
	
	public TableInfoList()
	{
		this.init_TableInfo();		
	}
	
	public TableInfo getsqls(String tablename)
	{
		if(this.m.containsKey(tablename))
			return m.get(tablename);
		else
			return null;
	}
	private void init_TableInfo()
	{
		TableInfo ti = new TableInfo();
		ti.name = tableName.t_sqlite_master;
		ti.selectsql = "SELECT type, name "+
		"FROM "+ ti.name +
		"WHERE name='" +
		tableName.t_weibo +
		"'";
		m.put(ti.name, ti);
		
		ti.name = tableName.t_users;
		ti.createsql = "CREATE TABLE "+ ti.name +
		"("+
		"   _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"   user_id INTEGER UNIQUE NOT NULL AUTOINCREMENT,"+
		"   first_use_time INTEGER NOT NULL DEFAULT 0,"+
		"   birth INTEGER NOT NULL DEFAULT 0,"+
		"   gender INTEGER NOT NULL DEFAULT 0 ,"+
		"   email TEXT NOT NULL  DEFAULT '',"+
		"   passwd TEXT NOT NULL  DEFAULT '',"+
		"   first_reg_time INTEGER NOT NULL DEFAULT 0,"+
		"   isuser INTEGER NOT NULL DEFAULT 0,"+
		"   ts INTEGER NOT NULL AUTOINCREMENT"+
		");";
		ti.selectsql = "SELECT _id, user_id,first_use_time,birth,gender,email,passwd,first_reg_time,isuser,ts " +
				"FROM " + ti.name;
		ti.updatesql = "UPDATE  "+ ti.name +
		"set user_id = ?,birth = ?,gender = ?,email = ?,passwd = ?,first_reg_time = ?,isuser = ?,ts = ?";
		
		ti.insertsql = "INSERT INTO " + ti.name +
				" (user_id,first_use_time,birth,gender,email,passwd,first_reg_time,isuser,ts)"+
				"VALUES (?,?,?,?,?,?,?,?,?)";

		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_clients;
		ti.createsql = "CREATE TABLE "+ ti.name +
		"("+
		"   _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"   user_id INTEGER UNIQUE NOT NULL AUTOINCREMENT,"+
		"   client_id INTEGER NOT NULL AUTOINCREMENT,"+
		"   imsi TEXT NOT NULL DEFAULT '',"+
		"   imei TEXT NOT NULL DEFAULT '',"+
		"   phone_number TEXT NOT NULL DEFAULT '',"+
		"   ts INTEGER NOT NULL AUTOINCREMENT"+
		");";
		ti.selectsql = "SELECT client_id"+
		"FROM " + ti.name;

		ti.updatesql = "UPDATE "+ ti.name +
		"SET client_id = ?";

		ti.insertsql = "INSERT INTO " + ti.name +
		" (client_id) VALUES (?)"+

		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_weibo;
		ti.createsql = "create table "+ ti.name +
		"("+
		"   _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"   user_id INTEGER NOT NULL DEFAULT -1,"+
		"   weibo_id INTEGER NOT NULL DEFAULT 1,"+
		"   weibo_account TEXT NOT NULL,   "+
		"   credential TEXT NOT NULL,"+
		"   ts INTEGER NOT NULL AUTOINCREMENT"+
		");";
		
		ti.selectsql = "SELECT weibo_account, credential , ts"+
		"		FROM "+ ti.name +
		"		WHERE ts > ?";
				
		ti.insertsql = " INSERT INTO  " + ti.name +
				"(user_id, weibo_id, weibo_account, credential, ts)"+
				"VALUES (-1, 1, ?, ?,?)";
		

		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_foods;
		ti.createsql = "create table "+ ti.name +
		"("+
		"   _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"   food_id INTEGER UNIQUE NOT NULL AUTOINCREMENT,"+
		"   food_prop_id INTEGER NOT NULL DEFAULT -1,"+
		"   foodname TEXT NOT NULL DEFAULT '',"+
		"   unit TEXT NOT NULL DEFAULT '',"+
		"   category TEXT NOT NULL DEFAULT '',"+
		"   ts INTEGER NOT NULL AUTOINCREMENT"+
		");";

		ti.insertsql = "INSERT INTO " + ti.name +
				"(food_id,food_prop_id,foodname,unit,category,ts)" +
		"VALUES (?,?,?,?,?,?)";
		
		ti.selectsql = "SELECT food_id,food_prop_id,foodname,unit,category,ts " +
		"  FROM " + ti.name + " ";
		
		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_food_prop;
		ti.createsql = "create table "+ ti.name +
		"("+
		"   _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"   food_prop_id INTEGER UNIQUE NOT NULL,"+
		"   food_prop TEXT NOT NULL,"+
		"   unit TEXT NOT NULL DEFAULT '',"+
		"   ifcount INTEGER NOT NULL DEFAULT 0,"+
		"   ts INTEGER NOT NULL AUTOINCREMENT"+
		");";
		ti.insertsql = "INSERT INTO " + ti.name +
				"(food_prop_id,unit,ifcount,ts) " +
				"VALUES (?,?,?,?)	";					

		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_feeds;
		ti.createsql = "create table "+ ti.name +
		"("+
		"   _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"   food_id INTEGER NOT NULL,"+
		"   user_id INTEGER NOT NULL,"+
		"   client_id INTEGER NOT NULL,"+
		"   r_date INTEGER NOT NULL,"+
		"   r_time INTEGER NOT NULL,"+
		"   quantity REAL NOT NULL,"+
		"   mark INT NOT NULL DEFAULT 1,"+
		"   ts INTEGER NOT NULL AUTOINCREMENT"+
		");";
		ti.selectsql = "SELECT _id,food_id,user_id,client_id,r_date,r_time,quantity,mark,ts"+
		"	FROM " + ti.name + " WHERE (client_id = ? or client_id = -1 ) and ts > ?";
			
		ti.insertsql = "INSERT INTO  " + ti.name +
				"(food_id,user_id,client_id,r_date,r_time,quantity,mark,ts)"+
				"	VALUES (?, ?, ?, ?, ?, ?, 1 ,?)";
		
		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_tips;
		ti.createsql = "create table "+ ti.name +
		"("+
		"   _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"   dayage INTEGER NOT NULL,"+
		"   content TEXT NOT NULL,"+
		"   ts INTEGER NOT NULL AUTOINCREMENT"+
		");";
		
		ti.insertsql = "INSERT INTO " + ti.name +
				"(dayage, content, ts) VALUES (?, ?, ?)";
		
		ti.selectsql = "SELECT content FROM " + ti.name;

		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_feedbacks;
		ti.createsql = "create table "+ ti.name +
		"("+
		"   _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"   user_id INTEGER NOT NULL,"+
		"   r_time INTEGER,"+
		"   content TEXT NOT NULL DEFAULT ''  "+
		"   ts INTEGER NOT NULL AUTOINCREMENT" +
		");";

		ti.selectsql = "SELECT r_time, content,ts" + 
		"FROM " + ti.name;
		

		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_local_avg;
		ti.createsql = "create table "+ ti.name +
		"("+
		"_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"food_prop_id INTEGER NOT NULL,"+
		"r_date INTEGER NOT NULL,"+
		"food_prop TEXT NOT NULL,"+
		"yours float,"+
		"avgs float,"+
		"diffs float,"+
		"ts INTEGER NOT NULL AUTOINCREMENT"+
		") ;";

		ti.insertsql = "INSERT INTO " + ti.name +
				"(food_prop_id, r_date,food_prop,yours,avgs,diffs,ts)" +
				"VALUES(?,?,?,?,?,?,?) ";
		
		ti.selectsql = "SELECT _id, food_prop_id, r_date,food_prop,yours,avgs,diffs,ts " +
				"FROM " + ti.name;
		
		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_ourcontact;
		ti.createsql = "create table "+ ti.name +
		"("+
		"   _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"   name TEXT,"+
		"   value TEXT ,"+
		"   ts INTEGER NOT NULL AUTOINCREMENT"+
		");";

		ti.insertsql = "INSERT INTO " + ti.name +
		"(name, value,ts)" +
		"VALUES(?,?,?) ";
		
		ti.selectsql = "SELECT _id,name, value,ts FROM " + ti.name;
		
		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_loadinfo;
		ti.createsql = "create table "+ ti.name +
		"("+
		"   _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"   content TEXT,"+
		"   ts INTEGER NOT NULL AUTOINCREMENT"+
		");";

		ti.insertsql = "INSERT INTO " + ti.name +
		"(content,ts)" +
		"VALUES(?,?) ";
		
		ti.selectsql = "SELECT _id,content,ts FROM " + ti.name;
		

		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_avg_height;
		ti.createsql = "create table "+ ti.name +
		"("+
		"   _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"   age INTEGER NOT NULL,"+
		"   gender INTEGER NOT NULL,"+
		"   min REAL,"+
		"   mid REAL,"+
		"   max REAL,"+
		"   ts INTEGER NOT NULL AUTOINCREMENT"+
		");";
		
		ti.insertsql = "INSERT INTO " + ti.name +
		"(age,gender,min,mid,max,ts)" +
		"VALUES(?,?,?,?,?,?) ";
		
		ti.selectsql = "SELECT _id,min,mid,max,ts FROM " + ti.name + 
		" WHERE age=? and gender = ?";
		

		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_avg_weight;
		ti.createsql = "create table "+ ti.name +
		"("+
		"   _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"   age INTEGER NOT NULL,"+
		"   gender INTEGER NOT NULL,"+
		"   min REAL,"+
		"   mid REAL,"+
		"   max REAL,"+
		"   ts INTEGER NOT NULL AUTOINCREMENT"+
		");";

		ti.insertsql = "INSERT INTO " + ti.name +
		"(age,gender,min,mid,max,ts)" +
		"VALUES(?,?,?,?,?,?) ";
		
		ti.selectsql = "SELECT _id,min,mid,max,ts FROM " + ti.name + 
		" WHERE age=? and gender = ?";
		
		
		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_huser;
		ti.createsql = "create table "+ ti.name +
		"("+
		"    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"    user_id INTEGER NOT NULL,"+
		"	 client_id INTEGER NOT NULL," +
		"    num REAL,"+
		"    ts INTEGER NOT NULL AUTOINCREMENT"+
		");";
		
		ti.insertsql = "INSERT INTO " + ti.name +
		"(user_id,client_id,num,ts)" +
		"VALUES(?,?,?,?) ";
		
		ti.selectsql = "SELECT user_id,client_id,num,ts FROM " + ti.name +
		   " WHERE ts > ? ";

		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_wuser;
		ti.createsql = "create table "+ ti.name +
		"("+
		"    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"    user_id INTEGER NOT NULL,    "+
		"	 client_id INTEGER NOT NULL," +
		"    num REAL,"+
		"    ts INTEGER NOT NULL AUTOINCREMENT"+
		");";

		ti.insertsql = "INSERT INTO " + ti.name +
		"(user_id,client_id,num,ts)" +
		"VALUES(?,?,?,?) ";
		
		ti.selectsql = "SELECT user_id,client_id,num,ts FROM " + ti.name;
		
		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_food_ux;
		ti.createsql = "create table "+ ti.name +
		"("+
		"    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"    user_id INTEGER NOT NULL,"+
		"    food_id INTEGER NOT NULL,"+
		"    d1 REAL NOT NULL,"+
		"    d2 REAL NOT NULL,"+
		"    d3 REAL NOT NULL,"+
		"    d4 REAL NOT NULL,"+
		"    d5 REAL NOT NULL,"+
		"    min REAL NOT NULL,"+
		"    max REAL NOT NULL,"+
		"    ts INTEGER NOT NULL AUTOINCREMENT"+
		");";

		ti.insertsql = "INSERT INTO " + ti.name +
		"(user_id,food_id,d1,d2,d3,d4,d5,min,max,ts)" +
		"VALUES(?,?,?,?,?,?,?,?,?,?) ";
		
		ti.selectsql = "SELECT food_id,d1,d2,d3,d4,d5,min,max,ts " +
				"FROM " + ti.name +
				" WHERE food_id = ?";
		
		ti.updatesql = "UPDATE " + ti.name + 
		"SET d1 = ?, d2 = ?, d3 = ?, d4 = ?, d5 =?" +
		"WHERE food_id = ?";
		
		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_food_default;
		ti.createsql = "create table "+ ti.name +
		"("+
		"    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"    age INTEGER NOT NULL,"+
		"    gender INTEGER NOT NULL,"+
		"    food_id INTEGER NOT NULL,"+
		"    d1 REAL NOT NULL,"+
		"    d2 REAL NOT NULL,"+
		"    d3 REAL NOT NULL,"+
		"    d4 REAL NOT NULL,"+
		"    d5 REAL NOT NULL,"+
		"    min REAL NOT NULL,"+
		"    max REAL NOT NULL,"+
		"    ts INTEGER NOT NULL AUTOINCREMENT"+
		");";

		ti.insertsql = "INSERT INTO " + ti.name +
		"(age,gender,food_id,d1,d2,d3,d4,d5,min,max,ts)" +
		"VALUES(?,?,?,?,?,?,?,?,?,?,?) ";
		
		ti.selectsql = "SELECT food_id,d1,d2,d3,d4,d5,min,max,ts " +
				"FROM " + ti.name +
				" WHERE  age = ? and gender = ?";
		
		
		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_h_default;
		ti.createsql = "create table "+ ti.name +
		"("+
		"    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"    age INTEGER NOT NULL,"+
		"    gender INTEGER NOT NULL,"+
		"    d1 REAL NOT NULL,"+
		"    d2 REAL NOT NULL,"+
		"    d3 REAL NOT NULL,"+
		"    d4 REAL NOT NULL,"+
		"    d5 REAL NOT NULL,"+
		"    min REAL NOT NULL,"+
		"    max REAL NOT NULL,"+
		"    ts INTEGER NOT NULL AUTOINCREMENT"+
		");";

		ti.insertsql = "INSERT INTO " + ti.name +
		"(age,gender,d1,d2,d3,d4,d5,min,max,ts)" +
		"VALUES(?,?,?,?,?,?,?,?,?,?) ";
		
		ti.selectsql = "SELECT d1,d2,d3,d4,d5,min,max,ts " +
				"FROM " + ti.name +
				" WHERE age = ? and gender = ?";
		
		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_h_ux;
		ti.createsql = "create table "+ ti.name +
		"("+
		"    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"    user_id INTEGER NOT NULL,"+
		"    d1 REAL NOT NULL,"+
		"    d2 REAL NOT NULL,"+
		"    d3 REAL NOT NULL,"+
		"    d4 REAL NOT NULL,"+
		"    d5 REAL NOT NULL,"+
		"    min REAL NOT NULL,"+
		"    max REAL NOT NULL,"+
		"    ts INTEGER NOT NULL AUTOINCREMENT"+
		");";

		ti.insertsql = "INSERT INTO " + ti.name +
		"(user_id,d1,d2,d3,d4,d5,min,max,ts)" +
		"VALUES(?,?,?,?,?,?,?,?,?) ";
		
		ti.selectsql = "SELECT d1,d2,d3,d4,d5,min,max,ts " +
				"FROM " + ti.name ;
				
		
		ti.updatesql = "UPDATE " + ti.name + 
		"SET d1 = ?, d2 = ?, d3 = ?, d4 = ?, d5 =?" ;
		
		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_w_default;
		ti.createsql = "create table "+ ti.name +
		"("+
		"    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"    age INTEGER NOT NULL,"+
		"    gender INTEGER NOT NULL,"+
		"    d1 REAL NOT NULL,"+
		"    d2 REAL NOT NULL,"+
		"    d3 REAL NOT NULL,"+
		"    d4 REAL NOT NULL,"+
		"    d5 REAL NOT NULL,"+
		"    min REAL NOT NULL,"+
		"    max REAL NOT NULL,"+
		"    ts INTEGER NOT NULL AUTOINCREMENT"+
		");";

		ti.insertsql = "INSERT INTO " + ti.name +
		"(age,gender,d1,d2,d3,d4,d5,min,max,ts)" +
		"VALUES(?,?,?,?,?,?,?,?,?,?) ";
		
		ti.selectsql = "SELECT d1,d2,d3,d4,d5,min,max,ts " +
				"FROM " + ti.name +
				"WHERE age = ? and gender = ?";
		
		m.put(ti.name, ti);

		ti = new TableInfo();
		ti.name = tableName.t_w_ux;
		ti.createsql = "create table "+ ti.name +
		"("+
		"    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,"+
		"    user_id INTEGER NOT NULL,    "+
		"    d1 REAL NOT NULL,"+
		"    d2 REAL NOT NULL,"+
		"    d3 REAL NOT NULL,"+
		"    d4 REAL NOT NULL,"+
		"    d5 REAL NOT NULL,"+
		"    min REAL NOT NULL,"+
		"    max REAL NOT NULL,"+
		"    ts INTEGER NOT NULL AUTOINCREMENT"+
		");";

		ti.insertsql = "INSERT INTO " + ti.name +
		"(user_id,d1,d2,d3,d4,d5,min,max,ts)" +
		"VALUES(?,?,?,?,?,?,?,?,?) ";
		
		ti.selectsql = "SELECT d1,d2,d3,d4,d5,min,max,ts " +
				"FROM " + ti.name ;
				
		
		ti.updatesql = "UPDATE " + ti.name + 
		"SET d1 = ?, d2 = ?, d3 = ?, d4 = ?, d5 =?" ;
		
		m.put(ti.name, ti);

	}
}
